Top Clients – Count

Top Clients

GOALS:
0
Identify top clients in 2020 and what characteristics they shared.
0
What sets the top clients apart from other clients?
0
How can we identify which clients could become top clients in future years?
0
What characteristics may indicate that our clients will cancel?
0
Future Changes/Investigations
0

Part 1. Data Cleaning + Initial Data Summary
0

https://lucid.app/lucidchart/c990c66e-0623-4b15-8ac5-76676292b84d/edit
0

0

I explicitly used SQL queries to demonstrate my data cleaning process for the Clients table. In reality, I used the order by and filter features in the interactive table and went back to write queries to describe what I was looking for. For the remaining tables, I only included SQL queries for things that I needed to fix.
0

Clients Table

Untitled

NCES_DISTRICT_ID

CLIENT_ADD_DATE

CLIENT_CANCEL_DATE

CITY

STATE

DISTRICT_TYPE

LOWEST_GRADE

HIGHEST_GRADE

LEVEL

NUMBER_OF_SCHOOLS

STATUS

CHARTER_DISTRICT

Use ⌥ + scroll to pan the table

12 columns, 1,825 rows

3.9 seconds (16 hours ago)

0

Schema of Columns

Untitled

TABLE_CATALOG

TABLE_SCHEMA

TABLE_NAME

COLUMN_NAME

DATA_TYPE

Use ⌥ + scroll to pan the table

5 columns, 348 rows

2.5 seconds (16 hours ago)

0

Check for Duplicates in Clients Table

Untitled

NCES_DISTRICT_ID

COUNT

Use ⌥ + scroll to pan the table

2 columns, 0 rows

2.5 seconds (16 hours ago)

0

Nulls in district_ID Column in Clients Table

Untitled

NCES_DISTRICT_ID

CLIENT_ADD_DATE

CLIENT_CANCEL_DATE

CITY

STATE

DISTRICT_TYPE

LOWEST_GRADE

HIGHEST_GRADE

LEVEL

NUMBER_OF_SCHOOLS

STATUS

CHARTER_DISTRICT

Use ⌥ + scroll to pan the table

12 columns, 0 rows

2.3 seconds (16 hours ago)

0

Verification that NCES_District_ID has only Numerical Values

Untitled

NCES_DISTRICT_ID

NCES_DISTRICT_ID2

Use ⌥ + scroll to pan the table

2 columns, 1,825 rows

2.4 seconds (16 hours ago)

0

District ID, Client_Add_Date, and Client_Cancel_Date

Untitled

NCES_DISTRICT_ID

CLIENT_ADD_DATE

CLIENT_CANCEL_DATE

Use ⌥ + scroll to pan the table

3 columns, 1,825 rows

0.8 seconds (17 hours ago)

0

Former Clients Active Account Time

Untitled

NCES_DISTRICT_ID

CLIENT_ADD_DATE

CLIENT_CANCEL_DATE

DAYS_DIFF

Use ⌥ + scroll to pan the table

4 columns, 286 rows

1.2 seconds (17 hours ago)

0

Clients with Client_Cancel_Date but no Client_Add_Date

Untitled

NCES_DISTRICT_ID

CLIENT_ADD_DATE

CLIENT_CANCEL_DATE

Use ⌥ + scroll to pan the table

3 columns, 26 rows

1.3 seconds (17 hours ago)

0

Initial Join Check Between Clients and Transactions to Look For Missing Values

--I looked at the transactions of the clients with no client_add_date. All of these clients have no 
transactions. I decided to remove these clients from my analysis because I was unsure if client_cancel_date 
and client_add_date were switched or if their data was lost during an earlier data migration.
SELECT c.nces_district_id, 
  c.client_add_date,
  c.client_cancel_date,
  t.payment_date,
  t.payment_volume,
  t.payment_qty
FROM clients c
LEFT JOIN transactions t
  ON c.nces_district_id = t.nces_district_id
WHERE client_add_date is NULL 
  AND client_cancel_date is NOT NULL;

Untitled

NCES_DISTRICT_ID

CLIENT_ADD_DATE

CLIENT_CANCEL_DATE

PAYMENT_DATE

PAYMENT_VOLUME

PAYMENT_QTY

Use ⌥ + scroll to pan the table

6 columns, 26 rows

1.5 seconds (18 hours ago)

0

Possible Data Errors in Clients Table

-- I looked at cases where both client_add_date and client_cancel_date were null. Unlike when client_add_date 
as missing but client_cancel_date was non-null, this table shows that there were many cases that had recorded 
transactions. I decided to remove the rows with no transactions but keep rows with transactions but no add/
cancel dates.
SELECT c.nces_district_id, 
  c.client_add_date,
  c.client_cancel_date,
  t.payment_date,
  t.payment_volume,
  t.payment_qty
FROM clients c
LEFT JOIN transactions t
  ON c.nces_district_id = t.nces_district_id
WHERE client_add_date is NULL 
  AND client_cancel_date is NULL
ORDER BY nces_district_id, payment_date;

Untitled

NCES_DISTRICT_ID

CLIENT_ADD_DATE

CLIENT_CANCEL_DATE

PAYMENT_DATE

PAYMENT_VOLUME

PAYMENT_QTY

Use ⌥ + scroll to pan the table

6 columns, 10,000+ rows

3.5 seconds (18 hours ago)

0

Nulls in City or State Column in Clients Table

Untitled

NCES_DISTRICT_ID

CITY

STATE

Use ⌥ + scroll to pan the table

3 columns, 0 rows

2.4 seconds (16 hours ago)

0

Number of Distinct States

Untitled

STATE_COUNT_CHECK

Use ⌥ + scroll to pan the table

1 column, 1 row

2.4 seconds (16 hours ago)

0

Nulls in District_Type Column of Clients Table

Untitled

NCES_DISTRICT_ID

DISTRICT_TYPE

Use ⌥ + scroll to pan the table

2 columns, 0 rows

2.4 seconds (16 hours ago)

0

Distribution of District Types in Clients Table

Untitled

DISTRICT_TYPE

COUNT

Use ⌥ + scroll to pan the table

2 columns, 8 rows

1.7 seconds (18 hours ago)

0

Nulls in Lowest_grade, Highest_grade, and Level Columns in Clients Table

Untitled

NCES_DISTRICT_ID

LOWEST_GRADE

HIGHEST_GRADE

LEVEL

Use ⌥ + scroll to pan the table

4 columns, 0 rows

2.4 seconds (16 hours ago)

0

Frequency of Lowest_Grades in Clients Table

Untitled

LOWEST_GRADE

COUNT

Use ⌥ + scroll to pan the table

2 columns, 14 rows

2.4 seconds (16 hours ago)

0

Frequency of Highest_Grades in Clients Table

Untitled

HIGHEST_GRADE

COUNT

Use ⌥ + scroll to pan the table

2 columns, 13 rows

2.4 seconds (16 hours ago)

0

Frequency of Levels in Clients Table

Untitled

LEVEL

COUNT

Use ⌥ + scroll to pan the table

2 columns, 7 rows

2.4 seconds (16 hours ago)

0

Overview of District_Type in Clients Table

--Clarify what these District_Types are with team: I was expecting them to all have M or N. **Note to self: 
Levels marked may contain smaller subsets of grades but not grades from two groups. It may be helpful to 
analyze using lowest-highest grades or reclassify "Other" as the list of levels it contains.
SELECT nces_district_id, district_type, lowest_grade, highest_grade, level
FROM clients
WHERE district_type LIKE '%agency'
  OR district_type LIKE '%center'

Untitled

NCES_DISTRICT_ID

DISTRICT_TYPE

LOWEST_GRADE

HIGHEST_GRADE

LEVEL

Use ⌥ + scroll to pan the table

5 columns, 16 rows

2.4 seconds (16 hours ago)

0

Possible Instances of Incorrect Data

--List of districts to double-check with team because of unexpected values.
SELECT nces_district_id, district_type, lowest_grade, highest_grade, level
FROM clients 
WHERE level = 'Ungraded'
  OR level = 'Not reported'
  OR level = 'Not applicable'
  OR highest_grade = 'N'
  OR highest_grade = 'M'
  OR highest_grade = '13'
  OR highest_grade = '4'
  OR highest_grade = '11'
  OR lowest_grade = 'N'
  OR lowest_grade = 'M'
  OR lowest_grade = '10'
  OR lowest_grade = '11'

Untitled

NCES_DISTRICT_ID

DISTRICT_TYPE

LOWEST_GRADE

HIGHEST_GRADE

LEVEL

Use ⌥ + scroll to pan the table

5 columns, 11 rows

2.4 seconds (16 hours ago)

0

Frequency of Number of Schools Per District

Frequency of Number of Schools Per District

NUMBER_OF_SCHOOLS

COUNT

Use ⌥ + scroll to pan the table

2 columns, 79 rows

2.4 seconds (16 hours ago)

0

Districts With No Schools

Districts With No Associated Schools

NCES_DISTRICT_ID

NUMBER_OF_SCHOOLS

DISTRICT_TYPE

LOWEST_GRADE

HIGHEST_GRADE

Use ⌥ + scroll to pan the table

5 columns, 12 rows

1.1 seconds (18 hours ago)

0

Number of Nulls in Status Column

Untitled

STATUS

NCES_DISTRICT_ID

Use ⌥ + scroll to pan the table

2 columns, 0 rows

2.4 seconds (16 hours ago)

0

Number of District_IDs per Status Category (All WERE OPEN)

Untitled

STATUS

COUNT

Use ⌥ + scroll to pan the table

2 columns, 1 row

2.4 seconds (16 hours ago)

0

Number of Null Values in charter_district Column

Untitled

CHARTER_DISTRICT

NCES_DISTRICT_ID

Use ⌥ + scroll to pan the table

2 columns, 0 rows

2.4 seconds (16 hours ago)

0

Number of Charter Vs Non-Charter Districts

Untitled

CHARTER_DISTRICT

COUNT

Use ⌥ + scroll to pan the table

2 columns, 2 rows

2.4 seconds (16 hours ago)

0

Transactions Table

Untitled

NCES_DISTRICT_ID

PAYMENT_DATE

PAYMENT_TYPE

PAYMENT_VOLUME

PAYMENT_QTY

_SDC_BATCHED_AT

_SDC_RECEIVED_AT

_SDC_SEQUENCE

_SDC_TABLE_VERSION

Use ⌥ + scroll to pan the table

9 columns, 10,000+ rows

4.7 seconds (16 hours ago)

0

Number of Rows in Transactions Table

Untitled

total district_ids

Use ⌥ + scroll to pan the table

1 column, 1 row

2.4 seconds (16 hours ago)

0

Number of Unique District Ids in Transactions Table

Untitled

unique district_ids

Use ⌥ + scroll to pan the table

1 column, 1 row

2.4 seconds (16 hours ago)

0

Number of Transactions by District ID from Transactions Table

Untitled

NCES_DISTRICT_ID

number of transactions

Use ⌥ + scroll to pan the table

2 columns, 1,406 rows

2 seconds (17 hours ago)

0

Number of Transactions by District ID

Use ⌥ + scroll to pan the visual

2 columns, 1,406 rows

0

Enrollment Table

Untitled

NCES_DISTRICT_ID

REPORT_YEAR

SCHOOL_YEAR

STUDENT_COUNT

LOAD_DATE

FILENAME

Use ⌥ + scroll to pan the table

6 columns, 10,000+ rows

4.2 seconds (16 hours ago)

0

Total Rows in Enrollment Table

Untitled

total district_ids

Use ⌥ + scroll to pan the table

1 column, 1 row

2.4 seconds (16 hours ago)

0

Total Unique District IDs in Enrollment Table

Untitled

unique district_ids

Use ⌥ + scroll to pan the table

1 column, 1 row

2.4 seconds (16 hours ago)

0

Change Report Year and School Year Format in Enrollment Table

--Create code for changing report_year to report_year_fall and school_year and school_year_fall
--(I chose to cast as a number now, so I don't forget later, even though the table preview adds a comma)
SELECT nces_district_id,
  CAST(SUBSTR(report_year, 1, 4) AS number) AS report_year_fall,
  CAST(SUBSTR(school_year, 1, 4) AS number) AS school_year_fall,
  load_date,
  student_count
FROM enrollment;

Untitled

NCES_DISTRICT_ID

REPORT_YEAR_FALL

SCHOOL_YEAR_FALL

LOAD_DATE

STUDENT_COUNT

Use ⌥ + scroll to pan the table

5 columns, 10,000+ rows

4.1 seconds (16 hours ago)

0

Partially Revised Enrollment Table

--Checked to ensure report_year_fall and school_year_fall match and that there are no reports loaded from the 
future
WITH enrollment_r 
  AS 
    (
    SELECT nces_district_id,
      CAST(SUBSTR(report_year, 1, 4) AS number) AS report_year_fall,
      CAST(SUBSTR(school_year, 1, 4) AS number) AS school_year_fall,
      load_date,
      student_count
    FROM enrollment
    )
SELECT nces_district_id, report_year_fall 
FROM enrollment_r
WHERE report_year_fall != school_year_fall
  OR YEAR(load_date) < report_year_fall;

Untitled

NCES_DISTRICT_ID

REPORT_YEAR_FALL

Use ⌥ + scroll to pan the table

2 columns, 0 rows

2.4 seconds (16 hours ago)

0

I was cautious about trying to look at trends over time within the enrollment (and finances) table because the most recent data was from 2018. Ultimately, I decided to just take the most recent year of data form the enrollment table and the finances table. Originally, I had planned to filter out any data from before the pandemic, but decided to temporarily settle on using the most recent (non-null) report_year.
0
*If this were an actual project for work, I would ask the team responsible for ETL to update the data from https://nces.ed.gov/
, although it looks like the most recent completed dataset is only from 2019-2020. (If I was allowed to use outside sources for this project, I would upload both datasets to Databricks and do my analysis with the updated data. In an ideal world, I would wait until the 2020-2021 data was also available so I could look for trends over time.)
0

Enrollment_Revised Table

--revised enrollment table with only most recent (non-null) year of data
WITH enrollment_revised
  AS
  (
  WITH enrollment_r 
  AS 
    (
    SELECT nces_district_id,
      CAST(SUBSTR(report_year, 1, 4) AS number) AS report_year_fall,
      CAST(SUBSTR(school_year, 1, 4) AS number) AS school_year_fall,
      load_date,
      student_count
    FROM enrollment
    )
    SELECT a.nces_district_id, a.most_recent_report_year, b.student_count
    FROM 
      (
      SELECT nces_district_id, MAX(report_year_fall) as most_recent_report_year
      FROM enrollment_r
      WHERE report_year_fall = school_year_fall
        AND YEAR(load_date) >= report_year_fall
      GROUP BY nces_district_id
      ) a
    LEFT JOIN enrollment_r b
      ON a.nces_district_id = b.nces_district_id
      AND a.most_recent_report_year = b.report_year_fall
    WHERE student_count IS NOT NULL
  )
SELECT * FROM enrollment_revised

Untitled

NCES_DISTRICT_ID

MOST_RECENT_REPORT_YEAR

STUDENT_COUNT

Use ⌥ + scroll to pan the table

3 columns, 10,000+ rows

3.4 seconds (20 hours ago)

0

Finances Table

Finances

NCES_DISTRICT_ID

REPORT_YEAR

TOTAL_REVENUE

TOTAL_EXPENSES

LOCAL_REVENUE

LOAD_DATE

FILENAME

Use ⌥ + scroll to pan the table

7 columns, 10,000+ rows

5 seconds (18 hours ago)

0

Total Rows in Finances Table 

Untitled

total district_ids

Use ⌥ + scroll to pan the table

1 column, 1 row

3 seconds (18 hours ago)

0

Total Unique District_ids in Finances Table

Untitled

unique district_ids

Use ⌥ + scroll to pan the table

1 column, 1 row

2.7 seconds (18 hours ago)

0

finances table

Untitled

NCES_DISTRICT_ID

REPORT_YEAR

TOTAL_REVENUE

TOTAL_EXPENSES

LOCAL_REVENUE

LOAD_DATE

FILENAME

Use ⌥ + scroll to pan the table

7 columns, 0 rows

2.7 seconds (18 hours ago)

0

finances_r table

WITH finances_r
  AS 
    (
    SELECT nces_district_id,
      CAST(SUBSTR(report_year, 1, 4) AS number) AS report_year_fall,
      total_revenue,
      total_expenses,
      local_revenue,
      load_date
    FROM finances 
    WHERE total_revenue IS NOT NULL
      AND total_expenses IS NOT NULL
      AND local_revenue IS NOT NULL
    )
SELECT *
FROM finances_r;

finances_r

NCES_DISTRICT_ID

REPORT_YEAR_FALL

TOTAL_REVENUE

TOTAL_EXPENSES

LOCAL_REVENUE

LOAD_DATE

Use ⌥ + scroll to pan the table

6 columns, 10,000+ rows

4.3 seconds (18 hours ago)

0

finances_revised table

WITH finances_revised 
  AS 
  (
  WITH finances_r
    AS 
    (
    SELECT nces_district_id,
      CAST(SUBSTR(report_year, 1, 4) AS number) AS report_year_fall,
      total_revenue,
      total_expenses,
      local_revenue,
      load_date
    FROM finances 
    WHERE total_revenue IS NOT NULL
      AND total_expenses IS NOT NULL
      AND local_revenue IS NOT NULL
    )
    SELECT a.nces_district_id, a.most_recent_report_year, b.total_revenue, b.total_expenses, b.local_revenue
    FROM 
      (
      SELECT nces_district_id, MAX(report_year_fall) as most_recent_report_year
      FROM finances_r
      WHERE YEAR(load_date) >= report_year_fall
      GROUP BY nces_district_id
      ) a
    LEFT JOIN finances_r b
      ON a.nces_district_id = b.nces_district_id
      AND a.most_recent_report_year = b.report_year_fall
    WHERE a.most_recent_report_year IS NOT NULL
      AND b.total_revenue IS NOT NULL 
      AND b.total_expenses IS NOT NULL 
      AND b.local_revenue IS NOT NULL
  )
SELECT *
FROM finances_revised;

finances_revised

NCES_DISTRICT_ID

MOST_RECENT_REPORT_YEAR

TOTAL_REVENUE

TOTAL_EXPENSES

LOCAL_REVENUE

Use ⌥ + scroll to pan the table

5 columns, 10,000+ rows

3.6 seconds (20 hours ago)

0

Part 2: ID Top Clients of 2020
0

I defined "Top Clients"  based on highest net payment volume from 1/1/2020 to 12/31/2020.
0
*I was told that payment volume was closely correlated with revenue. Since I was not able to determine costs (such as set-up/maintenance costs for different sized clients) I just used net payment volume during 2020 as my KPI (Key Performance Indicator).
0

Client_Transactions_2020 Table

SELECT c.nces_district_id, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_volume, 
payment_qty 
FROM clients c 
LEFT JOIN transactions t 
  ON c.nces_district_id = t.nces_district_id 
WHERE YEAR(client_add_date) <= '2020'
  AND (client_cancel_date IS NULL OR client_cancel_date >= '2020')
  AND YEAR(payment_date) = '2020'

Untitled

NCES_DISTRICT_ID

CLIENT_ADD_DATE

CLIENT_CANCEL_DATE

CITY

STATE

DISTRICT_TYPE

LOWEST_GRADE

HIGHEST_GRADE

LEVEL

NUMBER_OF_SCHOOLS

STATUS

CHARTER_DISTRICT

PAYMENT_DATE

PAYMENT_TYPE

PAYMENT_VOLUME

PAYMENT_QTY

Use ⌥ + scroll to pan the table

16 columns, 10,000+ rows

5 seconds (16 hours ago)

0

Total Rows in client_transactions_2020 Table

WITH client_transactions_2020
 AS 
    (
    SELECT c.nces_district_id, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_volume, 
payment_qty
    FROM clients c 
    LEFT JOIN transactions t 
      ON c.nces_district_id = t.nces_district_id 
    WHERE YEAR(client_add_date) <= '2020'
      AND (client_cancel_date IS NULL OR client_cancel_date >= '2020')
      AND YEAR(payment_date) = '2020'
    )
SELECT COUNT(*) AS "total rows"
FROM client_transactions_2020;
    

Untitled

total rows

Use ⌥ + scroll to pan the table

1 column, 1 row

2.4 seconds (16 hours ago)

0

Total Unique District_IDs

   WITH client_transactions_2020
    AS 
    (
    SELECT c.nces_district_id, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_volume, 
payment_qty 
    FROM clients c 
    LEFT JOIN transactions t 
      ON c.nces_district_id = t.nces_district_id 
    WHERE YEAR(client_add_date) <= '2020'
      AND (client_cancel_date IS NULL OR client_cancel_date >= '2020')
      AND YEAR(payment_date) = '2020'
    )
SELECT COUNT(DISTINCT nces_district_id) AS "total unique district_ids"
FROM client_transactions_2020;

Untitled

total unique district_ids

Use ⌥ + scroll to pan the table

1 column, 1 row

2.4 seconds (16 hours ago)

0

Districts Ordered By Net Volume

--List of districts with highest to lowest net volume from 2020
   WITH client_transactions_2020
    AS 
    (
    SELECT c.nces_district_id, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_volume, 
payment_qty 
    FROM clients c 
    LEFT JOIN transactions t 
      ON c.nces_district_id = t.nces_district_id 
    WHERE YEAR(client_add_date) <= '2020'
      AND (client_cancel_date IS NULL OR client_cancel_date >= '2020')
      AND YEAR(payment_date) = '2020'
    )
SELECT nces_district_id, SUM(payment_volume) AS net_payment_volume
FROM client_transactions_2020
GROUP BY nces_district_id 
ORDER BY SUM(payment_volume) DESC;

Untitled

NCES_DISTRICT_ID

NET_PAYMENT_VOLUME

Use ⌥ + scroll to pan the table

2 columns, 1,269 rows

0.6 seconds (18 hours ago)

0

Net Income 2020 Based on Net Volume

Use ⌥ + scroll to pan the visual

2 columns, 1,269 rows

2

client_transactions_2020

--List of districts ranked based on highest to lowest net income from 2020
   WITH client_transactions_2020
    AS 
    (
    SELECT c.nces_district_id, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_volume, 
payment_qty 
    FROM clients c 
    LEFT JOIN transactions t 
      ON c.nces_district_id = t.nces_district_id 
    WHERE YEAR(client_add_date) <= '2020'
      AND (client_cancel_date IS NULL OR client_cancel_date >= '2020')
      AND YEAR(payment_date) = '2020'
    )
SELECT nces_district_id, net_payment_volume, DENSE_RANK() OVER(ORDER BY net_payment_volume DESC) as rank, 
client_add_date, client_cancel_date, city, state, district_type, lowest_grade, highest_grade, level, 
number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
FROM 
  (
  SELECT nces_district_id, SUM(payment_volume) OVER(PARTITION BY nces_district_id ORDER BY nces_district_id) 
AS net_payment_volume, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
  FROM client_transactions_2020
  ORDER BY net_payment_volume DESC
  )
ORDER BY rank ASC;

Untitled

NCES_DISTRICT_ID

NET_PAYMENT_VOLUME

RANK

CLIENT_ADD_DATE

CLIENT_CANCEL_DATE

CITY

STATE

DISTRICT_TYPE

LOWEST_GRADE

HIGHEST_GRADE

LEVEL

NUMBER_OF_SCHOOLS

STATUS

CHARTER_DISTRICT

PAYMENT_DATE

PAYMENT_TYPE

PAYMENT_QTY

Use ⌥ + scroll to pan the table

17 columns, 10,000+ rows

3.5 seconds (20 hours ago)

0

Mean Payment Volume

--mean volume
   WITH client_transactions_2020
    AS 
    (
    SELECT c.nces_district_id, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_volume, 
payment_qty 
    FROM clients c 
    LEFT JOIN transactions t 
      ON c.nces_district_id = t.nces_district_id 
    WHERE YEAR(client_add_date) <= '2020'
      AND (client_cancel_date IS NULL OR client_cancel_date >= '2020')
      AND YEAR(payment_date) = '2020'
    )
SELECT AVG(net_payment_volume) as average_payment_volume 
FROM 
  (
  SELECT nces_district_id, SUM(payment_volume) AS net_payment_volume
  FROM client_transactions_2020
  GROUP BY nces_district_id 
  ORDER BY SUM(payment_volume) DESC
  );

Untitled

AVERAGE_PAYMENT_VOLUME

Use ⌥ + scroll to pan the table

1 column, 1 row

1 second (18 hours ago)

0

Median Payment Volume

--median payment volume
   WITH client_transactions_2020
    AS 
    (
    SELECT c.nces_district_id, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_volume, 
payment_qty 
    FROM clients c 
    LEFT JOIN transactions t 
      ON c.nces_district_id = t.nces_district_id 
    WHERE YEAR(client_add_date) <= '2020'
      AND (client_cancel_date IS NULL OR client_cancel_date >= '2020')
      AND YEAR(payment_date) = '2020'
    )
SELECT MEDIAN(net_payment_volume) as median_net_payment_volume 
FROM 
  (
  SELECT nces_district_id, SUM(payment_volume) AS net_payment_volume
  FROM client_transactions_2020
  GROUP BY nces_district_id 
  ORDER BY SUM(payment_volume) DESC
  );

Untitled

MEDIAN_NET_PAYMENT_VOLUME

Use ⌥ + scroll to pan the table

1 column, 1 row

0.9 seconds (20 hours ago)

0

--Since there are 1,269 different clients (nces_district_id), the top 1% would be approximately the top 12 ranked clients by net_income generated in 2020.
0
(Normally, I would have done this in a temp table, but I did not have permissions to create one with this sample dataset. I also would have left 1% as a variable if I were doing this in R or created it as an adjustable value in a dashboard on Tableau using parameters/add action. However, since this was an exercise in SQL, I left it at 1%.)
0

Part 2: Data Analysis
0

Top Clients
0

Clients Categorized by Top 1%

--Categorized clients (nces_district_id) as top 1% or not top 1%
--I chose to use partition by nces_district_id instead of grouping by it so I would minimize future joins and 
allow me to simply copy-paste the code for future analyses. Since I partitioned, I had to use dense_rank over 
net_payment_volume to ensure that I didn't double-count districts. Looking back, I probably would have just 
used group by instead to increase readability.
   WITH client_transactions_2020
    AS 
    (
    SELECT c.nces_district_id, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_volume, 
payment_qty 
    FROM clients c 
    LEFT JOIN transactions t 
      ON c.nces_district_id = t.nces_district_id 
    WHERE YEAR(client_add_date) <= '2020'
      AND (client_cancel_date IS NULL OR client_cancel_date >= '2020')
      AND YEAR(payment_date) = '2020'
    )
SELECT nces_district_id, net_payment_volume, DENSE_RANK() OVER(ORDER BY net_payment_volume DESC) as rank, 
  CASE 
    WHEN rank <= 12 THEN 'TRUE'
    ELSE 'FALSE'
    END AS is_top_1_percent,
  client_add_date, client_cancel_date, city, state, district_type, lowest_grade, highest_grade, level, 
number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
FROM 
  (
  SELECT nces_district_id, SUM(payment_volume) OVER(PARTITION BY nces_district_id ORDER BY nces_district_id) 
AS net_payment_volume, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
  FROM client_transactions_2020
  ORDER BY net_payment_volume DESC
  )
ORDER BY rank ASC;

Categorized clients (nces_district_id) as top 1% or not top 1%

NCES_DISTRICT_ID

NET_PAYMENT_VOLUME

RANK

IS_TOP_1_PERCENT

CLIENT_ADD_DATE

CLIENT_CANCEL_DATE

CITY

STATE

DISTRICT_TYPE

LOWEST_GRADE

HIGHEST_GRADE

LEVEL

NUMBER_OF_SCHOOLS

STATUS

CHARTER_DISTRICT

PAYMENT_DATE

PAYMENT_TYPE

PAYMENT_QTY

Use ⌥ + scroll to pan the table

18 columns, 10,000+ rows

3.8 seconds (19 hours ago)

0

client_transactions_2020_ranked

--Same thing as above, but adusted to be more copy-pastable template for future references. Again, I normally 
would have used a temp table for this, so please excuse the mess.
WITH client_transactions_2020_ranked
  AS 
  (
   WITH client_transactions_2020
    AS 
    (
    SELECT c.nces_district_id, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_volume, 
payment_qty 
    FROM clients c 
    LEFT JOIN transactions t 
      ON c.nces_district_id = t.nces_district_id 
    WHERE YEAR(client_add_date) <= '2020'
      AND (client_cancel_date IS NULL OR client_cancel_date >= '2020')
      AND YEAR(payment_date) = '2020'
    )
SELECT nces_district_id, net_payment_volume, DENSE_RANK() OVER(ORDER BY net_payment_volume DESC) as rank, 
  CASE 
    WHEN rank <= 12 THEN 'TRUE'
    ELSE 'FALSE'
    END AS is_top_1_percent,
  client_add_date, client_cancel_date, city, state, district_type, lowest_grade, highest_grade, level, 
number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
FROM 
  (
  SELECT nces_district_id, SUM(payment_volume) OVER(PARTITION BY nces_district_id ORDER BY nces_district_id) 
AS net_payment_volume, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
  FROM client_transactions_2020
  ORDER BY net_payment_volume DESC
  )
ORDER BY rank ASC
)
SELECT *
FROM client_transactions_2020_ranked
ORDER BY rank ASC;

Untitled

NCES_DISTRICT_ID

NET_PAYMENT_VOLUME

RANK

IS_TOP_1_PERCENT

CLIENT_ADD_DATE

CLIENT_CANCEL_DATE

CITY

STATE

DISTRICT_TYPE

LOWEST_GRADE

HIGHEST_GRADE

LEVEL

NUMBER_OF_SCHOOLS

STATUS

CHARTER_DISTRICT

PAYMENT_DATE

PAYMENT_TYPE

PAYMENT_QTY

Use ⌥ + scroll to pan the table

18 columns, 10,000+ rows

4.1 seconds (19 hours ago)

0

What characteristics set the top 1% of clients apart from the rest?
0

State and Number of Districts

--Average Net Income and Number of Districts by State and Classification (is_top_1_percent)
WITH client_transactions_2020_ranked
  AS 
  (
   WITH client_transactions_2020
    AS 
    (
    SELECT c.nces_district_id, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_volume, 
payment_qty 
    FROM clients c 
    LEFT JOIN transactions t 
      ON c.nces_district_id = t.nces_district_id 
    WHERE YEAR(client_add_date) <= '2020'
      AND (client_cancel_date IS NULL OR client_cancel_date >= '2020')
      AND YEAR(payment_date) = '2020'
    )
SELECT nces_district_id, net_payment_volume, DENSE_RANK() OVER(ORDER BY net_payment_volume DESC) as rank, 
  CASE 
    WHEN rank <= 12 THEN 'TRUE'
    ELSE 'FALSE'
    END AS is_top_1_percent,
  client_add_date, client_cancel_date, city, state, district_type, lowest_grade, highest_grade, level, 
number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
FROM 
  (
  SELECT nces_district_id, SUM(payment_volume) OVER(PARTITION BY nces_district_id ORDER BY nces_district_id) 
AS net_payment_volume, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
  FROM client_transactions_2020
  ORDER BY net_payment_volume DESC
  )
ORDER BY rank ASC
)
SELECT state, is_top_1_percent, AVG(net_volume_district) AS average_volume, COUNT(DISTINCT nces_district_id) 
as num_districts
FROM 
  (
  SELECT nces_district_id, MAX(net_payment_volume) AS net_volume_district, state, is_top_1_percent 
  FROM client_transactions_2020_ranked
  GROUP BY nces_district_id, state, is_top_1_percent
  )
GROUP BY state, is_top_1_percent
ORDER BY average_volume DESC;

Untitled

STATE

IS_TOP_1_PERCENT

AVERAGE_VOLUME

NUM_DISTRICTS

Use ⌥ + scroll to pan the table

4 columns, 47 rows

1.8 seconds (19 hours ago)

0

https://public.tableau.com/views/NumberofClientsinTop1byState/Dashboard1
1

Florida primarily uses FOCUS SIS which is currently not supported. Florida has the 3rd largest population, and might be a good target for developing future software to expand compatibility with school information systems. It might be worthwhile to find data on most common SIS software by state to increase marketability.
0

State, City, Number of Districts

--Average Net Income and Number of Districts and Classification (is_top_1_percent) by 
--State, City 
WITH client_transactions_2020_ranked
  AS 
  (
   WITH client_transactions_2020
    AS 
    (
    SELECT c.nces_district_id, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_volume, 
payment_qty 
    FROM clients c 
    LEFT JOIN transactions t 
      ON c.nces_district_id = t.nces_district_id 
    WHERE YEAR(client_add_date) <= '2020'
      AND (client_cancel_date IS NULL OR client_cancel_date >= '2020')
      AND YEAR(payment_date) = '2020'
    )
SELECT nces_district_id, net_payment_volume, DENSE_RANK() OVER(ORDER BY net_payment_volume DESC) as rank, 
  CASE 
    WHEN rank <= 12 THEN 'TRUE'
    ELSE 'FALSE'
    END AS is_top_1_percent,
  client_add_date, client_cancel_date, city, state, district_type, lowest_grade, highest_grade, level, 
number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
FROM 
  (
  SELECT nces_district_id, SUM(payment_volume) OVER(PARTITION BY nces_district_id ORDER BY nces_district_id) 
AS net_payment_volume, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
  FROM client_transactions_2020
  ORDER BY net_payment_volume DESC
  )
ORDER BY rank ASC
)
SELECT state, city, is_top_1_percent, AVG(net_volume_district) AS average_volume, COUNT(DISTINCT 
nces_district_id) as num_districts
FROM 
  (
  SELECT nces_district_id, MAX(net_payment_volume) AS net_volume_district, state, city, is_top_1_percent 
  FROM client_transactions_2020_ranked
  GROUP BY nces_district_id, state, city, is_top_1_percent
  )
GROUP BY state, is_top_1_percent, city
ORDER BY average_volume ASC;

State, City, Number of Districts

STATE

CITY

IS_TOP_1_PERCENT

AVERAGE_VOLUME

NUM_DISTRICTS

Use ⌥ + scroll to pan the table

5 columns, 1,169 rows

1.3 seconds (4 hours ago)

0

Looking at the worst performing cities, Howell, MI is actually losing money. If we sort by number of districts (num_districts) descending, we see that Phoenix has 7 districts (highest number per city) as clients but makes significantly less money than the average district.
0

Note: I might want to go back and fix the capitalization for city names, although this shouldn't impact results since I didn't see any duplicates with all caps.
0

District Type Distribution For Top Performing Clients 

--Average Net Volume and Number of Districts and Classification (is_top_1_percent) by 
--District Type
WITH client_transactions_2020_ranked
  AS 
  (
   WITH client_transactions_2020
    AS 
    (
    SELECT c.nces_district_id, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_volume, 
payment_qty 
    FROM clients c 
    LEFT JOIN transactions t 
      ON c.nces_district_id = t.nces_district_id 
    WHERE YEAR(client_add_date) <= '2020'
      AND (client_cancel_date IS NULL OR client_cancel_date >= '2020')
      AND YEAR(payment_date) = '2020'
    )
SELECT nces_district_id, net_payment_volume, DENSE_RANK() OVER(ORDER BY net_payment_volume DESC) as rank, 
  CASE 
    WHEN rank <= 12 THEN 'TRUE'
    ELSE 'FALSE'
    END AS is_top_1_percent,
  client_add_date, client_cancel_date, city, state, district_type, lowest_grade, highest_grade, level, 
number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
FROM 
  (
  SELECT nces_district_id, SUM(payment_volume) OVER(PARTITION BY nces_district_id ORDER BY nces_district_id) 
AS net_payment_volume, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
  FROM client_transactions_2020
  ORDER BY net_payment_volume DESC
  )
ORDER BY rank ASC
)
SELECT district_type, is_top_1_percent, AVG(net_volume_district) AS average_volume, COUNT(DISTINCT 
nces_district_id) as num_districts
FROM 
  (
  SELECT nces_district_id, MAX(net_payment_volume) AS net_volume_district, is_top_1_percent, district_type 
  FROM client_transactions_2020_ranked
  GROUP BY nces_district_id, is_top_1_percent, district_type
  )
GROUP BY district_type, is_top_1_percent
ORDER BY average_volume DESC;

Untitled

DISTRICT_TYPE

IS_TOP_1_PERCENT

AVERAGE_VOLUME

NUM_DISTRICTS

Use ⌥ + scroll to pan the table

4 columns, 7 rows

28.8 seconds (19 hours ago)

0

We can see from the table that regular public school districts that are not a component of a supervisory union are the most profitable. All of the top 1% of clients are this district type. 
0

Number of Schools Per District For Top Performing Clients

--Average Net Volume and Number of Districts and Classification (is_top_1_percent) by 
--Average Number of Schools
WITH client_transactions_2020_ranked
  AS 
  (
   WITH client_transactions_2020
    AS 
    (
    SELECT c.nces_district_id, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_volume, 
payment_qty 
    FROM clients c 
    LEFT JOIN transactions t 
      ON c.nces_district_id = t.nces_district_id 
    WHERE YEAR(client_add_date) <= '2020'
      AND (client_cancel_date IS NULL OR client_cancel_date >= '2020')
      AND YEAR(payment_date) = '2020'
    )
SELECT nces_district_id, net_payment_volume, DENSE_RANK() OVER(ORDER BY net_payment_volume DESC) as rank, 
  CASE 
    WHEN rank <= 12 THEN 'TRUE'
    ELSE 'FALSE'
    END AS is_top_1_percent,
  client_add_date, client_cancel_date, city, state, district_type, lowest_grade, highest_grade, level, 
number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
FROM 
  (
  SELECT nces_district_id, SUM(payment_volume) OVER(PARTITION BY nces_district_id ORDER BY nces_district_id) 
AS net_payment_volume, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
  FROM client_transactions_2020
  ORDER BY net_payment_volume DESC
  )
ORDER BY rank ASC
)
, enrollment_revised
  AS
  (
  WITH enrollment_r 
  AS 
    (
    SELECT nces_district_id,
      CAST(SUBSTR(report_year, 1, 4) AS number) AS report_year_fall,
      CAST(SUBSTR(school_year, 1, 4) AS number) AS school_year_fall,
      load_date,
      student_count
    FROM enrollment
    )
    SELECT a.nces_district_id, a.most_recent_report_year, b.student_count
    FROM 
      (
      SELECT nces_district_id, MAX(report_year_fall) as most_recent_report_year
      FROM enrollment_r
      WHERE report_year_fall = school_year_fall
        AND YEAR(load_date) >= report_year_fall
      GROUP BY nces_district_id
      ) a
    LEFT JOIN enrollment_r b
      ON a.nces_district_id = b.nces_district_id
      AND a.most_recent_report_year = b.report_year_fall
    WHERE student_count IS NOT NULL
  )
  
SELECT c.nces_district_id, e.student_count, c.net_volume_district, c.is_top_1_percent
FROM 
  (
  SELECT nces_district_id, MAX(net_payment_volume) AS net_volume_district, is_top_1_percent
  FROM client_transactions_2020_ranked
  GROUP BY nces_district_id, is_top_1_percent, district_type
  ) c 
LEFT JOIN enrollment_revised e 
  ON c.nces_district_id = e.nces_district_id
ORDER BY net_volume_district DESC;

Untitled

NCES_DISTRICT_ID

STUDENT_COUNT

NET_VOLUME_DISTRICT

IS_TOP_1_PERCENT

Use ⌥ + scroll to pan the table

4 columns, 1,269 rows

1.1 seconds (17 hours ago)

0

Note: 4 of the 12 districts in the top 1% are missing student_count.
0

https://public.tableau.com/views/NetVolumePerDistrictVsStudentCount/Dashboard1
0

https://public.tableau.com/views/AverageStudentCountAverageNetVolumePerDistrictByTop1/Dashboard2
0

Charter District Status for Top 1%

WITH client_transactions_2020_ranked
  AS 
  (
   WITH client_transactions_2020
    AS 
    (
    SELECT c.nces_district_id, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_volume, 
payment_qty 
    FROM clients c 
    LEFT JOIN transactions t 
      ON c.nces_district_id = t.nces_district_id 
    WHERE YEAR(client_add_date) <= '2020'
      AND (client_cancel_date IS NULL OR client_cancel_date >= '2020')
      AND YEAR(payment_date) = '2020'
    )
SELECT nces_district_id, net_payment_volume, DENSE_RANK() OVER(ORDER BY net_payment_volume DESC) as rank, 
  CASE 
    WHEN rank <= 12 THEN 'TRUE'
    ELSE 'FALSE'
    END AS is_top_1_percent,
  client_add_date, client_cancel_date, city, state, district_type, lowest_grade, highest_grade, level, 
number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
FROM 
  (
  SELECT nces_district_id, SUM(payment_volume) OVER(PARTITION BY nces_district_id ORDER BY nces_district_id) 
AS net_payment_volume, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
  FROM client_transactions_2020
  ORDER BY net_payment_volume DESC
  )
)
SELECT charter_district, is_top_1_percent, AVG(net_payment_volume) as average_volume
FROM client_transactions_2020_ranked
GROUP BY is_top_1_percent, charter_district
ORDER BY is_top_1_percent DESC;

Untitled

CHARTER_DISTRICT

IS_TOP_1_PERCENT

AVERAGE_VOLUME

Use ⌥ + scroll to pan the table

3 columns, 3 rows

0.9 seconds (16 hours ago)

0

All of the top 1% are NOT charter schools.
0

Level of Top 1%

WITH client_transactions_2020_ranked
  AS 
  (
   WITH client_transactions_2020
    AS 
    (
    SELECT c.nces_district_id, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_volume, 
payment_qty 
    FROM clients c 
    LEFT JOIN transactions t 
      ON c.nces_district_id = t.nces_district_id 
    WHERE YEAR(client_add_date) <= '2020'
      AND (client_cancel_date IS NULL OR client_cancel_date >= '2020')
      AND YEAR(payment_date) = '2020'
    )
SELECT nces_district_id, net_payment_volume, DENSE_RANK() OVER(ORDER BY net_payment_volume DESC) as rank, 
  CASE 
    WHEN rank <= 12 THEN 'TRUE'
    ELSE 'FALSE'
    END AS is_top_1_percent,
  client_add_date, client_cancel_date, city, state, district_type, lowest_grade, highest_grade, level, 
number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
FROM 
  (
  SELECT nces_district_id, SUM(payment_volume) OVER(PARTITION BY nces_district_id ORDER BY nces_district_id) 
AS net_payment_volume, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
  FROM client_transactions_2020
  ORDER BY net_payment_volume DESC
  )
)
SELECT level, is_top_1_percent, AVG(net_payment_volume) as average_volume
FROM client_transactions_2020_ranked
GROUP BY is_top_1_percent, level
ORDER BY is_top_1_percent DESC;

Untitled

LEVEL

IS_TOP_1_PERCENT

AVERAGE_VOLUME

Use ⌥ + scroll to pan the table

3 columns, 9 rows

2.2 seconds (16 hours ago)

0

All of the top 1% of values are high school or other (meaning multiple levels).
0

I was leery to use data from the Finance and Enrollment tables because both had extremely out-of-date information (2016-17 was the most recent school year).
0

Total_Revenue of Top 1%

WITH client_transactions_2020_ranked
  AS 
  (
   WITH client_transactions_2020
    AS 
    (
    SELECT c.nces_district_id, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_volume, 
payment_qty 
    FROM clients c 
    LEFT JOIN transactions t 
      ON c.nces_district_id = t.nces_district_id 
    WHERE YEAR(client_add_date) <= '2020'
      AND (client_cancel_date IS NULL OR client_cancel_date >= '2020')
      AND YEAR(payment_date) = '2020'
    )
SELECT nces_district_id, net_payment_volume, DENSE_RANK() OVER(ORDER BY net_payment_volume DESC) as rank, 
  CASE 
    WHEN rank <= 12 THEN 'TRUE'
    ELSE 'FALSE'
    END AS is_top_1_percent,
  client_add_date, client_cancel_date, city, state, district_type, lowest_grade, highest_grade, level, 
number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
FROM 
  (
  SELECT nces_district_id, SUM(payment_volume) OVER(PARTITION BY nces_district_id ORDER BY nces_district_id) 
AS net_payment_volume, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
  FROM client_transactions_2020
  ORDER BY net_payment_volume DESC
  )
)
, finances_revised 
  AS 
  (
  WITH finances_r
    AS 
    (
    SELECT nces_district_id,
      CAST(SUBSTR(report_year, 1, 4) AS number) AS report_year_fall,
      total_revenue,
      total_expenses,
      local_revenue,
      load_date
    FROM finances 
    WHERE total_revenue IS NOT NULL
      AND total_expenses IS NOT NULL
      AND local_revenue IS NOT NULL
    )
    SELECT a.nces_district_id, a.most_recent_report_year, b.total_revenue, b.total_expenses, b.local_revenue
    FROM 
      (
      SELECT nces_district_id, MAX(report_year_fall) as most_recent_report_year
      FROM finances_r
      WHERE YEAR(load_date) >= report_year_fall
      GROUP BY nces_district_id
      ) a
    LEFT JOIN finances_r b
      ON a.nces_district_id = b.nces_district_id
      AND a.most_recent_report_year = b.report_year_fall
    WHERE a.most_recent_report_year IS NOT NULL
      AND b.total_revenue IS NOT NULL 
      AND b.total_expenses IS NOT NULL 
      AND b.local_revenue IS NOT NULL
  )
SELECT total_revenue, is_top_1_percent, AVG(net_payment_volume) as average_volume
FROM client_transactions_2020_ranked c
LEFT JOIN finances_revised f 
  ON c.nces_district_id = f.nces_district_id
GROUP BY is_top_1_percent, total_revenue
ORDER BY is_top_1_percent DESC;

Untitled

TOTAL_REVENUE

IS_TOP_1_PERCENT

AVERAGE_VOLUME

Use ⌥ + scroll to pan the table

3 columns, 1,156 rows

2.7 seconds (15 hours ago)

0

Total_Revenue of Top 1%

Use ⌥ + scroll to pan the visual

IS_TOP_1_PERCENT

FALSE

TRUE

3 columns, 1,156 rows

0

Total Expenses

--Average Net Income and Number of Districts and Classification (is_top_1_percent) by 
--total_expenses
WITH client_transactions_2020_ranked
  AS 
  (
   WITH client_transactions_2020
    AS 
    (
    SELECT c.nces_district_id, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_volume, 
payment_qty 
    FROM clients c 
    LEFT JOIN transactions t 
      ON c.nces_district_id = t.nces_district_id 
    WHERE YEAR(client_add_date) <= '2020'
      AND (client_cancel_date IS NULL OR client_cancel_date >= '2020')
      AND YEAR(payment_date) = '2020'
    )
SELECT nces_district_id, net_payment_volume, DENSE_RANK() OVER(ORDER BY net_payment_volume DESC) as rank, 
  CASE 
    WHEN rank <= 12 THEN 'TRUE'
    ELSE 'FALSE'
    END AS is_top_1_percent,
  client_add_date, client_cancel_date, city, state, district_type, lowest_grade, highest_grade, level, 
number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
FROM 
  (
  SELECT nces_district_id, SUM(payment_volume) OVER(PARTITION BY nces_district_id ORDER BY nces_district_id) 
AS net_payment_volume, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
  FROM client_transactions_2020
  ORDER BY net_payment_volume DESC
  )
)
, finances_revised
 AS 
  (
  WITH finances_r
    AS 
    (
    SELECT nces_district_id,
      CAST(SUBSTR(report_year, 1, 4) AS number) AS report_year_fall,
      total_revenue,
      total_expenses,
      local_revenue,
      load_date
    FROM finances 
    WHERE total_revenue IS NOT NULL
      AND total_expenses IS NOT NULL
      AND local_revenue IS NOT NULL
    )
    SELECT a.nces_district_id, a.most_recent_report_year, b.total_revenue, b.total_expenses, b.local_revenue
    FROM 
      (
      SELECT nces_district_id, MAX(report_year_fall) as most_recent_report_year
      FROM finances_r
      WHERE YEAR(load_date) >= report_year_fall
      GROUP BY nces_district_id
      ) a
    LEFT JOIN finances_r b
      ON a.nces_district_id = b.nces_district_id
      AND a.most_recent_report_year = b.report_year_fall
    WHERE a.most_recent_report_year IS NOT NULL
      AND b.total_revenue IS NOT NULL 
      AND b.total_expenses IS NOT NULL 
      AND b.local_revenue IS NOT NULL
  )
SELECT total_expenses, is_top_1_percent, AVG(net_payment_volume) as average_volume
FROM client_transactions_2020_ranked c
LEFT JOIN finances_revised f 
  ON c.nces_district_id = f.nces_district_id
GROUP BY is_top_1_percent, total_expenses
ORDER BY is_top_1_percent DESC;

Total Expenses

TOTAL_EXPENSES

IS_TOP_1_PERCENT

AVERAGE_VOLUME

Use ⌥ + scroll to pan the table

3 columns, 1,157 rows

3.3 seconds (15 hours ago)

0

Untitled

Use ⌥ + scroll to pan the visual

IS_TOP_1_PERCENT

FALSE

TRUE

3 columns, 1,157 rows

0

local_revenue

--Average Net Income and Number of Districts and Classification (is_top_1_percent) by 
--local_revenue
WITH client_transactions_2020_ranked
  AS 
  (
   WITH client_transactions_2020
    AS 
    (
    SELECT c.nces_district_id, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_volume, 
payment_qty 
    FROM clients c 
    LEFT JOIN transactions t 
      ON c.nces_district_id = t.nces_district_id 
    WHERE YEAR(client_add_date) <= '2020'
      AND (client_cancel_date IS NULL OR client_cancel_date >= '2020')
      AND YEAR(payment_date) = '2020'
    )
SELECT nces_district_id, net_payment_volume, DENSE_RANK() OVER(ORDER BY net_payment_volume DESC) as rank, 
  CASE 
    WHEN rank <= 12 THEN 'TRUE'
    ELSE 'FALSE'
    END AS is_top_1_percent,
  client_add_date, client_cancel_date, city, state, district_type, lowest_grade, highest_grade, level, 
number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
FROM 
  (
  SELECT nces_district_id, SUM(payment_volume) OVER(PARTITION BY nces_district_id ORDER BY nces_district_id) 
AS net_payment_volume, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
  FROM client_transactions_2020
  ORDER BY net_payment_volume DESC
  )
)
, finances_revised
AS 
  (
  WITH finances_r
    AS 
    (
    SELECT nces_district_id,
      CAST(SUBSTR(report_year, 1, 4) AS number) AS report_year_fall,
      total_revenue,
      total_expenses,
      local_revenue,
      load_date
    FROM finances 
    WHERE total_revenue IS NOT NULL
      AND total_expenses IS NOT NULL
      AND local_revenue IS NOT NULL
    )
    SELECT a.nces_district_id, a.most_recent_report_year, b.total_revenue, b.total_expenses, b.local_revenue
    FROM 
      (
      SELECT nces_district_id, MAX(report_year_fall) as most_recent_report_year
      FROM finances_r
      WHERE YEAR(load_date) >= report_year_fall
      GROUP BY nces_district_id
      ) a
    LEFT JOIN finances_r b
      ON a.nces_district_id = b.nces_district_id
      AND a.most_recent_report_year = b.report_year_fall
    WHERE a.most_recent_report_year IS NOT NULL
      AND b.total_revenue IS NOT NULL 
      AND b.total_expenses IS NOT NULL 
      AND b.local_revenue IS NOT NULL
  )
SELECT local_revenue, is_top_1_percent, AVG(net_payment_volume) as average_volume
FROM client_transactions_2020_ranked c
LEFT JOIN finances_revised f 
  ON c.nces_district_id = f.nces_district_id
GROUP BY is_top_1_percent, local_revenue
ORDER BY is_top_1_percent DESC;

Local Revenue

LOCAL_REVENUE

IS_TOP_1_PERCENT

AVERAGE_VOLUME

Use ⌥ + scroll to pan the table

3 columns, 1,152 rows

2 seconds (15 hours ago)

0

Untitled

Use ⌥ + scroll to pan the visual

IS_TOP_1_PERCENT

FALSE

TRUE

3 columns, 1,152 rows

0

Local Vs Total Revenue

WITH client_transactions_2020_ranked
  AS 
  (
   WITH client_transactions_2020
    AS 
    (
    SELECT c.nces_district_id, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_volume, 
payment_qty 
    FROM clients c 
    LEFT JOIN transactions t 
      ON c.nces_district_id = t.nces_district_id 
    WHERE YEAR(client_add_date) <= '2020'
      AND (client_cancel_date IS NULL OR client_cancel_date >= '2020')
      AND YEAR(payment_date) = '2020'
    )
SELECT nces_district_id, net_payment_volume, DENSE_RANK() OVER(ORDER BY net_payment_volume DESC) as rank, 
  CASE 
    WHEN rank <= 12 THEN 'TRUE'
    ELSE 'FALSE'
    END AS is_top_1_percent,
  client_add_date, client_cancel_date, city, state, district_type, lowest_grade, highest_grade, level, 
number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
FROM 
  (
  SELECT nces_district_id, SUM(payment_volume) OVER(PARTITION BY nces_district_id ORDER BY nces_district_id) 
AS net_payment_volume, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
  FROM client_transactions_2020
  ORDER BY net_payment_volume DESC
  )
)
, finances_revised
AS 
  (
  WITH finances_r
    AS 
    (
    SELECT nces_district_id,
      CAST(SUBSTR(report_year, 1, 4) AS number) AS report_year_fall,
      total_revenue,
      total_expenses,
      local_revenue,
      load_date
    FROM finances 
    WHERE total_revenue IS NOT NULL
      AND total_expenses IS NOT NULL
      AND local_revenue IS NOT NULL
    )
    SELECT a.nces_district_id, a.most_recent_report_year, b.total_revenue, b.total_expenses, b.local_revenue
    FROM 
      (
      SELECT nces_district_id, MAX(report_year_fall) as most_recent_report_year
      FROM finances_r
      WHERE YEAR(load_date) >= report_year_fall
      GROUP BY nces_district_id
      ) a
    LEFT JOIN finances_r b
      ON a.nces_district_id = b.nces_district_id
      AND a.most_recent_report_year = b.report_year_fall
    WHERE a.most_recent_report_year IS NOT NULL
      AND b.total_revenue IS NOT NULL 
      AND b.total_expenses IS NOT NULL 
      AND b.local_revenue IS NOT NULL
  )
SELECT local_revenue, total_revenue, is_top_1_percent, AVG(net_payment_volume) as average_volume
FROM client_transactions_2020_ranked c
LEFT JOIN finances_revised f 
  ON c.nces_district_id = f.nces_district_id
GROUP BY is_top_1_percent, local_revenue, total_revenue
ORDER BY is_top_1_percent DESC;

Untitled

LOCAL_REVENUE

TOTAL_REVENUE

IS_TOP_1_PERCENT

AVERAGE_VOLUME

Use ⌥ + scroll to pan the table

4 columns, 1,168 rows

3.2 seconds (9 hours ago)

0

Local Revenue Vs Total Revenue

Use ⌥ + scroll to pan the visual

IS_TOP_1_PERCENT

FALSE

TRUE

4 columns, 1,168 rows

0

https://public.tableau.com/views/LocalVsTotalRevenue/Dashboard1
0

District Net Income

--Average Net Income and Number of Districts and Classification (is_top_1_percent) by 
--district_net_income (total_revenue - total_expenses)
WITH client_transactions_2020_ranked
  AS 
  (
   WITH client_transactions_2020
    AS 
    (
    SELECT c.nces_district_id, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_volume, 
payment_qty 
    FROM clients c 
    LEFT JOIN transactions t 
      ON c.nces_district_id = t.nces_district_id 
    WHERE YEAR(client_add_date) <= '2020'
      AND (client_cancel_date IS NULL OR client_cancel_date >= '2020')
      AND YEAR(payment_date) = '2020'
    )
SELECT nces_district_id, net_payment_volume, DENSE_RANK() OVER(ORDER BY net_payment_volume DESC) as rank, 
  CASE 
    WHEN rank <= 12 THEN 'TRUE'
    ELSE 'FALSE'
    END AS is_top_1_percent,
  client_add_date, client_cancel_date, city, state, district_type, lowest_grade, highest_grade, level, 
number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
FROM 
  (
  SELECT nces_district_id, SUM(payment_volume) OVER(PARTITION BY nces_district_id ORDER BY nces_district_id) 
AS net_payment_volume, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
  FROM client_transactions_2020
  ORDER BY net_payment_volume DESC
  )
)
, finances_revised
AS 
  (
  WITH finances_r
    AS 
    (
      SELECT nces_district_id,
      CAST(SUBSTR(report_year, 1, 4) AS number) AS report_year_fall,
      total_revenue,
      total_expenses,
      local_revenue,
      load_date
    FROM finances 
    WHERE total_revenue IS NOT NULL
      AND total_expenses IS NOT NULL
      AND local_revenue IS NOT NULL
    )
    SELECT a.nces_district_id, a.most_recent_report_year, b.total_revenue, b.total_expenses, b.local_revenue
    FROM 
      (
      SELECT nces_district_id, MAX(report_year_fall) as most_recent_report_year
      FROM finances_r
      WHERE YEAR(load_date) >= report_year_fall
      GROUP BY nces_district_id
      ) a
    LEFT JOIN finances_r b
      ON a.nces_district_id = b.nces_district_id
      AND a.most_recent_report_year = b.report_year_fall
    WHERE a.most_recent_report_year IS NOT NULL
      AND b.total_revenue IS NOT NULL 
      AND b.total_expenses IS NOT NULL 
      AND b.local_revenue IS NOT NULL
  )
SELECT total_revenue - total_expenses as net_income, is_top_1_percent, AVG(net_payment_volume) as 
average_volume
FROM client_transactions_2020_ranked c
LEFT JOIN finances_revised f 
  ON c.nces_district_id = f.nces_district_id
GROUP BY is_top_1_percent, net_income
ORDER BY is_top_1_percent DESC;

District Net Income

NET_INCOME

IS_TOP_1_PERCENT

AVERAGE_VOLUME

Use ⌥ + scroll to pan the table

3 columns, 1,098 rows

2.1 seconds (15 hours ago)

0

Untitled

Use ⌥ + scroll to pan the visual

IS_TOP_1_PERCENT

FALSE

TRUE

3 columns, 1,098 rows

0

Number of Transactions In 2020

--number of transactions
WITH client_transactions_2020_ranked
  AS 
  (
   WITH client_transactions_2020
    AS 
    (
    SELECT c.nces_district_id, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_volume, 
payment_qty 
    FROM clients c 
    LEFT JOIN transactions t 
      ON c.nces_district_id = t.nces_district_id 
    WHERE YEAR(client_add_date) <= '2020'
      AND (client_cancel_date IS NULL OR client_cancel_date >= '2020')
      AND YEAR(payment_date) = '2020'
    )
SELECT nces_district_id, net_payment_volume, DENSE_RANK() OVER(ORDER BY net_payment_volume DESC) as rank, 
  CASE 
    WHEN rank <= 12 THEN 'TRUE'
    ELSE 'FALSE'
    END AS is_top_1_percent,
  client_add_date, client_cancel_date, city, state, district_type, lowest_grade, highest_grade, level, 
number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
FROM 
  (
  SELECT nces_district_id, SUM(payment_volume) OVER(PARTITION BY nces_district_id ORDER BY nces_district_id) 
AS net_payment_volume, client_add_date, client_cancel_date, city, state, district_type, lowest_grade, 
highest_grade, level, number_of_schools, status, charter_district, payment_date, payment_type, payment_qty
  FROM client_transactions_2020
  ORDER BY net_payment_volume DESC
  )
)
SELECT a.average_num_transactions, b.average_volume, a.is_top_1_percent
FROM
(
SELECT AVG(number_of_transactions) as average_num_transactions, is_top_1_percent 
FROM 
client_transactions_2020_ranked c
LEFT JOIN
  (
  SELECT nces_district_id, COUNT(*) AS number_of_transactions
  FROM transactions 
  GROUP BY nces_district_id
  ) t
ON c.nces_district_id = t.nces_district_id
GROUP BY is_top_1_percent
) a 
LEFT JOIN 
(
SELECT AVG(net_payment_volume) as average_volume, is_top_1_percent 
FROM 
client_transactions_2020_ranked c
LEFT JOIN
  (
  SELECT nces_district_id, COUNT(*) AS number_of_transactions
  FROM transactions 
  GROUP BY nces_district_id
  ) t
ON c.nces_district_id = t.nces_district_id
GROUP BY is_top_1_percent
) b
ON a.is_top_1_percent = b.is_top_1_percent

Untitled

AVERAGE_NUM_TRANSACTIONS

AVERAGE_VOLUME

IS_TOP_1_PERCENT

Use ⌥ + scroll to pan the table

3 columns, 2 rows

1.7 seconds (9 hours ago)

0

Untitled

Use ⌥ + scroll to pan the visual

IS_TOP_1_PERCENT

FALSE

TRUE

3 columns, 2 rows

0

Districts with more transactions are more likely to be in the top 1% of clients.
0

How can we identify which clients could become top clients in future years?
0

* Greater Number of Transactions
0
* Not Charter School
0
* Regular Public School that is Not a Component of a Supervisory Union
0
* Greater Number of Students (but less than 80,000)
0
* High School or Other (Multiple Grade Level Groups like K-12)
0
* Total Revenue Between $134,000,000 and $858,000,000
0

0

I wanted to search for the set of schools that were not current clients that had these characteristics, but the information was not available in this dataset. I would ask the data engineering team to add a table with charter_district, district_type, and level to the database. Alternatively, I could upload the dataset from SQL_TEST and the data publicly available via https://nces.ed.gov/
 to Microsoft SQL Server. (I couldn't use databricks, because the files would likely be over my upload data limit for the free community edition.)
0

What characteristics may indicate that our clients will cancel?
0

Account Activation Table

--Shows Clients table info and adds a column for account activation status.
WITH account_activation_clients
  AS 
  (
  SELECT nces_district_id, 
    client_add_date,
    client_cancel_date,
    days_diff,
    state,
    district_type,
    lowest_grade,
    highest_grade,
    level,
    number_of_schools,
    status,
    charter_district, 
    CASE 
      WHEN days_diff IS NULL THEN 'active'
      ELSE 'cancelled'
    END AS account_activation
  FROM 
    (
    SELECT nces_district_id,
      client_add_date,
      client_cancel_date,
      client_cancel_date - client_add_date AS days_diff,
      state,
      district_type,
      lowest_grade,
      highest_grade,
      level,
      number_of_schools,
      status,
      charter_district
    FROM clients
    )
  )
SELECT *
FROM account_activation_clients

Untitled

NCES_DISTRICT_ID

CLIENT_ADD_DATE

CLIENT_CANCEL_DATE

DAYS_DIFF

STATE

DISTRICT_TYPE

LOWEST_GRADE

HIGHEST_GRADE

LEVEL

NUMBER_OF_SCHOOLS

STATUS

CHARTER_DISTRICT

ACCOUNT_ACTIVATION

Use ⌥ + scroll to pan the table

13 columns, 1,825 rows

1.9 seconds (8 hours ago)

0

Cancelled By State

--Shows clients from highest to lowest cancel rate grouped by state
WITH account_activation_clients
  AS 
  (
  SELECT nces_district_id, 
    client_add_date,
    client_cancel_date,
    days_diff,
    state,
    district_type,
    lowest_grade,
    highest_grade,
    level,
    number_of_schools,
    status,
    charter_district, 
    CASE 
      WHEN days_diff IS NULL THEN 'active'
      ELSE 'cancelled'
    END AS account_activation
  FROM 
    (
    SELECT nces_district_id,
      client_add_date,
      client_cancel_date,
      client_cancel_date - client_add_date AS days_diff,
      state,
      district_type,
      lowest_grade,
      highest_grade,
      level,
      number_of_schools,
      status,
      charter_district
    FROM clients
    )
  )
SELECT state, num_active_accounts, num_cancelled_accounts, num_active_accounts / num_cancelled_accounts as 
ratio_active_to_cancelled
FROM 
  (
SELECT state, num_active_accounts, num_cancelled_accounts
FROM 
(
SELECT state, num_active_accounts, num_cancelled_accounts, COUNT(*) as check_duplicates
FROM 
  (
  SELECT state, MAX(num_active_accounts) OVER(PARTITION BY state) as num_active_accounts, MAX
(num_cancelled_accounts) OVER(PARTITION BY state) as num_cancelled_accounts
  FROM 
    (
    SELECT state,
    CASE WHEN account_activation = 'active' THEN count
      END as num_active_accounts,
    CASE WHEN account_activation = 'cancelled' THEN count
      END as num_cancelled_accounts
    FROM 
      (
      SELECT state, account_activation, COUNT(state) as count 
      FROM account_activation_clients
      GROUP BY state, account_activation 
      )
    )
  )
GROUP BY state, num_active_accounts, num_cancelled_accounts
)
  )
  ORDER BY ratio_active_to_cancelled ASC;

Cancelled BY State

STATE

NUM_ACTIVE_ACCOUNTS

NUM_CANCELLED_ACCOUNTS

RATIO_ACTIVE_TO_CANCELLED

Use ⌥ + scroll to pan the table

4 columns, 43 rows

0.7 seconds (5 hours ago)

0

Cancelled By District Type

----Shows clients from highest to lowest cancel rate grouped by district type
WITH account_activation_clients
  AS 
  (
  SELECT nces_district_id, 
    client_add_date,
    client_cancel_date,
    days_diff,
    state,
    district_type,
    lowest_grade,
    highest_grade,
    level,
    number_of_schools,
    status,
    charter_district, 
    CASE 
      WHEN days_diff IS NULL THEN 'active'
      ELSE 'cancelled'
    END AS account_activation
  FROM 
    (
    SELECT nces_district_id,
      client_add_date,
      client_cancel_date,
      client_cancel_date - client_add_date AS days_diff,
      state,
      district_type,
      lowest_grade,
      highest_grade,
      level,
      number_of_schools,
      status,
      charter_district
    FROM clients
    )
  )
SELECT district_type, num_active_accounts, num_cancelled_accounts, num_active_accounts / 
num_cancelled_accounts as ratio_active_to_cancelled
FROM 
  (
SELECT district_type, num_active_accounts, num_cancelled_accounts
FROM 
(
SELECT district_type, num_active_accounts, num_cancelled_accounts, COUNT(*) as check_duplicates
FROM 
  (
  SELECT district_type, MAX(num_active_accounts) OVER(PARTITION BY district_type) as num_active_accounts, MAX
(num_cancelled_accounts) OVER(PARTITION BY district_type) as num_cancelled_accounts
  FROM 
    (
    SELECT district_type,
    CASE WHEN account_activation = 'active' THEN count
      END as num_active_accounts,
    CASE WHEN account_activation = 'cancelled' THEN count
      END as num_cancelled_accounts
    FROM 
      (
      SELECT district_type, account_activation, COUNT(state) as count 
      FROM account_activation_clients
      GROUP BY district_type, account_activation 
      )
    )
  )
GROUP BY district_type, num_active_accounts, num_cancelled_accounts
)
  )
  ORDER BY ratio_active_to_cancelled ASC;

Ordered From Most at Risk of Cancelling to Least at Risk of Cancelling

DISTRICT_TYPE

NUM_ACTIVE_ACCOUNTS

NUM_CANCELLED_ACCOUNTS

RATIO_ACTIVE_TO_CANCELLED

Use ⌥ + scroll to pan the table

4 columns, 8 rows

1.5 seconds (5 hours ago)

0

Cancelled By Level

----Shows clients from highest to lowest cancel rate grouped by level
WITH account_activation_clients
  AS 
  (
  SELECT nces_district_id, 
    client_add_date,
    client_cancel_date,
    days_diff,
    state,
    district_type,
    lowest_grade,
    highest_grade,
    level,
    number_of_schools,
    status,
    charter_district, 
    CASE 
      WHEN days_diff IS NULL THEN 'active'
      ELSE 'cancelled'
    END AS account_activation
  FROM 
    (
    SELECT nces_district_id,
      client_add_date,
      client_cancel_date,
      client_cancel_date - client_add_date AS days_diff,
      state,
      district_type,
      lowest_grade,
      highest_grade,
      level,
      number_of_schools,
      status,
      charter_district
    FROM clients
    )
  )
SELECT level, num_active_accounts, num_cancelled_accounts, num_active_accounts / num_cancelled_accounts as 
ratio_active_to_cancelled
FROM 
  (
SELECT level, num_active_accounts, num_cancelled_accounts
FROM 
(
SELECT level, num_active_accounts, num_cancelled_accounts, COUNT(*) as check_duplicates
FROM 
  (
  SELECT level, MAX(num_active_accounts) OVER(PARTITION BY level) as num_active_accounts, MAX
(num_cancelled_accounts) OVER(PARTITION BY level) as num_cancelled_accounts
  FROM 
    (
    SELECT level,
    CASE WHEN account_activation = 'active' THEN count
      END as num_active_accounts,
    CASE WHEN account_activation = 'cancelled' THEN count
      END as num_cancelled_accounts
    FROM 
      (
      SELECT level, account_activation, COUNT(level) as count 
      FROM account_activation_clients
      GROUP BY level, account_activation 
      )
    )
  )
GROUP BY level, num_active_accounts, num_cancelled_accounts
)
  )
  ORDER BY ratio_active_to_cancelled ASC;

Ordered From Most at Risk of Cancelling to Least at Risk of Cancelling

LEVEL

NUM_ACTIVE_ACCOUNTS

NUM_CANCELLED_ACCOUNTS

RATIO_ACTIVE_TO_CANCELLED

Use ⌥ + scroll to pan the table

4 columns, 7 rows

1.7 seconds (5 hours ago)

0

Cancelled By Number of Schools

----Shows clients from highest to lowest cancel rate grouped by number of schools in district
WITH account_activation_clients
  AS 
  (
  SELECT nces_district_id, 
    client_add_date,
    client_cancel_date,
    days_diff,
    state,
    district_type,
    lowest_grade,
    highest_grade,
    level,
    number_of_schools,
    status,
    charter_district, 
    CASE 
      WHEN days_diff IS NULL THEN 'active'
      ELSE 'cancelled'
    END AS account_activation
  FROM 
    (
    SELECT nces_district_id,
      client_add_date,
      client_cancel_date,
      client_cancel_date - client_add_date AS days_diff,
      state,
      district_type,
      lowest_grade,
      highest_grade,
      level,
      number_of_schools,
      status,
      charter_district
    FROM clients
    )
  )
SELECT number_of_schools, num_active_accounts, num_cancelled_accounts, num_active_accounts / 
num_cancelled_accounts as ratio_active_to_cancelled
FROM 
  (
SELECT number_of_schools, num_active_accounts, num_cancelled_accounts
FROM 
(
SELECT number_of_schools, num_active_accounts, num_cancelled_accounts, COUNT(*) as check_duplicates
FROM 
  (
  SELECT number_of_schools, MAX(num_active_accounts) OVER(PARTITION BY number_of_schools) as 
num_active_accounts, MAX(num_cancelled_accounts) OVER(PARTITION BY number_of_schools) as num_cancelled_accounts
  FROM 
    (
    SELECT number_of_schools,
    CASE WHEN account_activation = 'active' THEN count
      END as num_active_accounts,
    CASE WHEN account_activation = 'cancelled' THEN count
      END as num_cancelled_accounts
    FROM 
      (
      SELECT number_of_schools, account_activation, COUNT(number_of_schools) as count 
      FROM account_activation_clients
      GROUP BY number_of_schools, account_activation 
      )
    )
  )
GROUP BY number_of_schools, num_active_accounts, num_cancelled_accounts
)
  )
  ORDER BY ratio_active_to_cancelled ASC;

Ordered From Most at Risk of Cancelling to Least at Risk of Cancelling

NUMBER_OF_SCHOOLS

NUM_ACTIVE_ACCOUNTS

NUM_CANCELLED_ACCOUNTS

RATIO_ACTIVE_TO_CANCELLED

Use ⌥ + scroll to pan the table

4 columns, 79 rows

1.4 seconds (5 hours ago)

0

Cancelled By Charter District (Charter of Non Charter

----Shows clients from highest to lowest cancel rate grouped by charter or non-charter
WITH account_activation_clients
  AS 
  (
  SELECT nces_district_id, 
    client_add_date,
    client_cancel_date,
    days_diff,
    state,
    district_type,
    lowest_grade,
    highest_grade,
    level,
    number_of_schools,
    status,
    charter_district, 
    CASE 
      WHEN days_diff IS NULL THEN 'active'
      ELSE 'cancelled'
    END AS account_activation
  FROM 
    (
    SELECT nces_district_id,
      client_add_date,
      client_cancel_date,
      client_cancel_date - client_add_date AS days_diff,
      state,
      district_type,
      lowest_grade,
      highest_grade,
      level,
      number_of_schools,
      status,
      charter_district
    FROM clients
    )
  )
SELECT charter_district, num_active_accounts, num_cancelled_accounts, num_active_accounts / 
num_cancelled_accounts as ratio_active_to_cancelled
FROM 
  (
SELECT charter_district, num_active_accounts, num_cancelled_accounts
FROM 
(
SELECT charter_district, num_active_accounts, num_cancelled_accounts, COUNT(*) as check_duplicates
FROM 
  (
  SELECT charter_district, MAX(num_active_accounts) OVER(PARTITION BY charter_district) as 
num_active_accounts, MAX(num_cancelled_accounts) OVER(PARTITION BY charter_district) as num_cancelled_accounts
  FROM 
    (
    SELECT charter_district,
    CASE WHEN account_activation = 'active' THEN count
      END as num_active_accounts,
    CASE WHEN account_activation = 'cancelled' THEN count
      END as num_cancelled_accounts
    FROM 
      (
      SELECT charter_district, account_activation, COUNT(charter_district) as count 
      FROM account_activation_clients
      GROUP BY charter_district, account_activation 
      )
    )
  )
GROUP BY charter_district, num_active_accounts, num_cancelled_accounts
)
  )
  ORDER BY ratio_active_to_cancelled ASC;

Ordered From Most at Risk of Cancelling to Least at Risk of Cancelling

CHARTER_DISTRICT

NUM_ACTIVE_ACCOUNTS

NUM_CANCELLED_ACCOUNTS

RATIO_ACTIVE_TO_CANCELLED

Use ⌥ + scroll to pan the table

4 columns, 2 rows

1.6 seconds (5 hours ago)

0

PART 5: CAVEATS AND FUTURE CONSIDERATIONS
0

Caveats:
0
Data from the Finances and Enrollment tables were out of date and also pre-pandemic. 
0
I tried to limit my analysis to data from after the pandemic began. As a former teacher, I have experienced first-hand the degree to which school needs, resources, and procedures have changed as a result of COVID-19. 
0
need for 1-1 devices may increase district likelihood to adopt this technology
0
early/mid pandemic (2020-2021): many schools were not permitted to collect items (such as paper money) or use shared classroom supplies (like sharing computers in a lab setting)
0
Most school districts prioritized updating technology and ensuring students of all ages had 1-1 devices for remote/hybrid learning. Additionally, most districts invested in increasing at-home internet access for low income students. Having internet at home and take-home laptops/ipads will likely make parents more likely to be able to use the app to pay electronically
0
The teacher/substitute shortage increases class size and decreases teacher planning time. Additionally, the adoption of hybrid learning and the in general increased teacher responsibilities during the pandemic leads to more stress and less time for teachers. Since collecting fees from fundraising/school fees is typically the homeroom teacher's responsibility, removing this extra task from teachers' long list of jobs would decrease teachers' workloads.
0
Missing/Incomplete Data
0
Future Directions:
0
I would like to have updated the finance and enrollment tables with data from after the pandemic started.
0
I didn't have time to use "case" statements to recategorize client grade Levels (other)
0
I didn't have time to look at average number of transactions/time gap between most recent transaction and cancellation rate.
0

0

Part 1. Data Cleaning + Initial Data Summary0

Part 2: ID Top Clients of 20200

Part 2: Data Analysis0

Top Clients0

What characteristics set the top 1% of clients apart from the rest?0

How can we identify which clients could become top clients in future years?0

* Greater Number of Transactions0

* Not Charter School0

* Regular Public School that is Not a Component of a Supervisory Union0

* Greater Number of Students (but less than 80,000)0

* High School or Other (Multiple Grade Level Groups like K-12)0

* Total Revenue Between $134,000,000 and $858,000,0000

What characteristics may indicate that our clients will cancel?0

Part 1. Data Cleaning + Initial Data Summary
0

Part 2: ID Top Clients of 2020
0

Part 2: Data Analysis
0

Top Clients
0

What characteristics set the top 1% of clients apart from the rest?
0

How can we identify which clients could become top clients in future years?
0

* Greater Number of Transactions
0

* Not Charter School
0

* Regular Public School that is Not a Component of a Supervisory Union
0

* Greater Number of Students (but less than 80,000)
0

* High School or Other (Multiple Grade Level Groups like K-12)
0

* Total Revenue Between $134,000,000 and $858,000,000
0

What characteristics may indicate that our clients will cancel?
0